// 从数据库中获取数据
const express = require('express')
const router = express.Router()
//导入数据库 sqlFun('sql',[],res=>{})
const sqlFun = require('./mysql')
//路由接口
//测试接口
router.get('/', (req, res) => {
  res.send('Hello World!')
})

/**
 * 商品列表: 获取分页{total:'', arr[{},{}],pagesize:8}  接口
 * 参数: page 页码
 */
router.get('/projectList', (req, res) => {
  const page = req.query.page || 1;
  // const sqlLen = "select * from project where id";
  const sqlLen = "select count(*) as total from project";
  sqlFun(sqlLen, null, data => {
    // let len = data.length;
    let len = data[0].total;
    console.log(len);
    const sql = "select * from project order by id desc limit 8 offset " + (page - 1) * 8;
    sqlFun(sql, null, result => {
      if (result.length > 0) {
        res.send({
          status: 200,
          data: result,
          pageSize: 8,
          total: len
        })
      } else {
        res.send({
          status: 500,
          msg: '暂无数据'
        })
      }
    })
  })
})

/**
 * 商品查询接口: search  接口
 * 参数: search
 */
router.get('/search', (req, res) => {
  var search = req.query.search;
  const sql = "select * from project where concat(`title`,`sellPoint`,`descs`) like '%" + search + "%'";
  sqlFun(sql, null, result => {
    if (result.length > 0) {
      res.send({
        status: 200,
        result
      })
    } else {
      res.send({
        status: 500,
        msg: '暂无数据'
      })
    }
  })
})

/**
 * 查询的结果进行分页显示并查询 接口
 * 参数: search, page
 */
router.get('/searchWithPage', (req, res) => {
  const search = req.query.search || '';
  const page = parseInt(req.query.page) || 1;
  const pageSize = 8;
  const offset = (page - 1) * pageSize;

  console.log('search', search);
  console.log('page', page);

  // 查询总数
  const sqlLen = `
    SELECT COUNT(*) AS total FROM project
    WHERE concat(title, sellPoint, descs) LIKE '%${search}%'
  `;

  console.log(sqlLen);

  sqlFun(sqlLen, null, countResult => {
    const total = countResult[0].total;

    // 查询当前页数据
    const sql = `
      SELECT * FROM project
      WHERE concat(title, sellPoint, descs) LIKE '%${search}%'
      ORDER BY id DESC
      LIMIT ${pageSize} OFFSET ${offset}
    `;

    console.log(sql);

    sqlFun(sql, null, result => {
      if (result.length > 0) {
        res.send({
          status: 200,
          data: result,
          total: total,
          pageSize: pageSize,
          currentPage: page
        });
      } else {
        res.send({
          status: 500,
          msg: '暂无数据'
        });
      }
    });
  });
});












module.exports = router
